package com.zhu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zhu.pojo.Customer;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface CustomerMapper extends BaseMapper<Customer> {
    @Select("select * from Customers where name=#{customer_name}")
    public Customer selectByName(String customer_name);

    @Insert("INSERT INTO Customers (name, old_name, address, region, contact_person, phone1,  manager, manager_phone, business_mgr, status, operator) " +
            "VALUES (#{name}, #{old_name}, #{address}, #{region}, #{contact_person}, #{phone1}, #{manager}, #{manager_phone}, #{business_mgr}, #{status}, #{operator})")
    public int insert(Customer c);

    @Update("UPDATE Customers SET  region = #{region}, contact_person = #{contact_person}, phone1 = #{phone1},operator = #{operator} ,status=#{status} WHERE id = #{id}")
    public int updateById(Customer c);
    @Select("select id from Customers where name=#{name}")
    public Integer selectIdByName(String customer_name);

    @Select("SELECT b.business_Name AS 业务名称, " +
            "COUNT(DISTINCT CASE WHEN b.business_Status = '正式业务' THEN cb.customer_Id END) AS 累计净增客户数, " +
            "COUNT(DISTINCT CASE WHEN b.business_Status = '正式业务' AND DATE_FORMAT(c.create_Time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') THEN cb.customer_Id END) AS 当月新增客户数, " +
            "COUNT(DISTINCT CASE WHEN c.status = '删除' AND DATE_FORMAT(c.create_Time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') THEN cb.customer_Id END) AS 当月删除客户数 " +
            "FROM Customers c " +
            "JOIN Customer_Business cb ON c.id = cb.customer_Id " +
            "JOIN Businesses b ON cb.business_Id = b.id " +
            "GROUP BY b.business_Name")
    List<Map<String, Object>> getCustomerStatistics();

    @Select("SELECT * FROM customers WHERE name LIKE CONCAT('%', #{name}, '%')")
    List<Map<String,Object>> searchAll(String name);

    @Delete("delete from customers where name =#{name}")
    void delete(String name);
}
